package com.lego.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lego.bean.Goods;
import com.lego.bean.Page;
import com.lego.dao.BaseDao;
import com.lego.dao.GoodsDao;

public class GoodsDaoImpl implements GoodsDao {

	@Override
	public List<Goods> selectGoods(int pageNum, int pageSize,String keyWord,String goodsType) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Goods> list = new ArrayList();
		List<Object> params = new ArrayList();
		String sql = "SELECT * FROM goods WHERE 1=1 ";
		if(keyWord!=null&&!("").equals(keyWord)) {
			sql+="AND (goodsType like concat('%',?,'%') || goodsName like concat('%',?,'%') )";
			params.add(keyWord);
			params.add(keyWord);
		}
		if(goodsType!=null&&!("").equals(goodsType)) {
			sql+="AND goodsType = ? ";
			params.add(goodsType);
		}
		sql+=" LIMIT ?,?";
		int newPageNum = (pageNum-1)*pageSize;
		params.add(newPageNum);
		params.add(pageSize);
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i+1, params.get(i));
			}
			System.out.println(ps);
			res = ps.executeQuery();
			while(res.next()) {
				Goods goods = new Goods();
				goods.setGoodsId(res.getInt("goodsId"));
				goods.setSellerId(res.getInt("sellerId"));
				goods.setGoodsName(res.getString("goodsName"));
				goods.setQuantity(res.getInt("quantity"));
				goods.setPrice(res.getDouble("price"));
				goods.setDescription(res.getString("description"));
				goods.setGoodsType(res.getString("goodsType"));
				goods.setGoodsImg(res.getString("goodsImg"));
				goods.setPublishTime(res.getDate("publishTime"));
				list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		return list;
	}

	@Override
	public int selectAllRows(String goodsType,String keyWord) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int rows = 0;
		List<Object> params = new ArrayList();
		String sql = "SELECT count(1) as totalRows FROM goods WHERE 1=1 ";
		if(keyWord!=null&&!("").equals(keyWord)) {
			sql+="AND (goodsType like concat('%',?,'%') || goodsName like concat('%',?,'%') )";
			params.add(keyWord);
			params.add(keyWord);
		}
		if(goodsType!=null&&!("").equals(goodsType)) {
			sql+="AND goodsType = ? ";
			params.add(goodsType);
		}
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i+1, params.get(i));
			}
			res = ps.executeQuery();
			if(res.next()) {
				rows = res.getInt("totalRows");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		return rows;
	}

	@Override
	public Goods selectByGoodsId(int goodsId) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Goods goods = null;
		String sql = "SELECT * FROM goods WHERE goodsId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setObject(1, goodsId);
			System.out.println(ps);
			res = ps.executeQuery();
			if(res.next()) {
				goods = new Goods();
				goods.setGoodsId(res.getInt("goodsId"));
				goods.setSellerId(res.getInt("sellerId"));
				goods.setGoodsName(res.getString("goodsName"));
				goods.setQuantity(res.getInt("quantity"));
				goods.setPrice(res.getDouble("price"));
				goods.setDescription(res.getString("description"));
				goods.setGoodsType(res.getString("goodsType"));
				goods.setGoodsImg(res.getString("goodsImg"));
				goods.setPublishTime(res.getDate("publishTime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		return goods;
	}

	@Override
	public int changeQuantity(int negativeNumber,int goodsId) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE goods SET quantity = quantity + ? WHERE goodsId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, negativeNumber);
			ps.setInt(2, goodsId);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updateGoods(Goods goods) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE goods SET goodsName=?,quantity=?,price=?,goodsType=? WHERE goodsId=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, goods.getGoodsName());
			ps.setInt(2, goods.getQuantity());
			ps.setDouble(3, goods.getPrice());
			ps.setString(4, goods.getGoodsType());
			ps.setInt(5, goods.getGoodsId());
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updateGoodsQuantity(int goodsId) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE goods SET quantity = 0 WHERE goodsId=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, goodsId);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int addGoods(Goods goods) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO goods(sellerId,goodsName,quantity,price,description,goodsImg,goodsType,publishTime) VALUES "
					+ "(?,?,?,?,?,?,?,now())";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, goods.getSellerId());
			ps.setString(2, goods.getGoodsName());
			ps.setInt(3, goods.getQuantity());
			ps.setDouble(4, goods.getPrice());
			ps.setString(5, goods.getDescription());
			ps.setString(6, goods.getGoodsImg());
			ps.setString(7,goods.getGoodsType());
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}
}
